--**************************************************************************************
--
-- File:    CleanUp.sql for Partitioning Transaction History Tables Example
--
-- Summary: Range partition tables TransactionHistory and TransactionHistoryArchive
--
-- Date:    June 07, 2005
--
-- SQL Server Version: 9.00.1256.00
--
--**************************************************************************************
-- This file is part of the Microsoft SQL Server Code Samples.
-- Copyright (C) Microsoft Corporation.  All rights reserved.
--
-- This source code is intended only as a supplement to Microsoft
-- Development Tools and/or on-line documentation.  See these other
-- materials for detailed information regarding Microsoft code samples.
--
-- THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
-- KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
-- IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
-- PARTICULAR PURPOSE.
--**************************************************************************************

USE [AdventureWorks];
GO

-- Drop foreign key constraints on TransactionHistory to allow table to be dropped
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'FK_TransactionHistory_Product_ProductID') AND parent_object_id = OBJECT_ID(N'[Production].[TransactionHistory]'))
ALTER TABLE [Production].[TransactionHistory] DROP CONSTRAINT [FK_TransactionHistory_Product_ProductID];
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Production].[TransactionHistory]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [Production].[TransactionHistory]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Production].[TransactionHistoryArchive]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [Production].[TransactionHistoryArchive];
GO


IF EXISTS (SELECT * FROM [sys].[partition_schemes] WHERE name = 'TransactionsPS1')
DROP PARTITION SCHEME [TransactionsPS1];
GO

IF EXISTS (SELECT * FROM [sys].[partition_functions] WHERE name = 'TransactionRangePF1')
DROP PARTITION FUNCTION [TransactionRangePF1]
GO

IF EXISTS (SELECT * FROM [sys].[partition_schemes] WHERE name = 'TransactionArchivePS2')
DROP PARTITION SCHEME [TransactionArchivePS2];
GO

IF EXISTS (SELECT * FROM [sys].[partition_functions] WHERE name = 'TransactionArchivePF2')
DROP PARTITION FUNCTION [TransactionArchivePF2]
GO


CREATE TABLE [Production].[TransactionHistory](
    [TransactionID] [int] IDENTITY (1, 1) NOT NULL,
    [ProductID] [int] NOT NULL,
    [ReferenceOrderID] [int] NOT NULL,
    [ReferenceOrderLineID] [int] NOT NULL CONSTRAINT [DF_TransactionHistory_ReferenceOrderLineID] DEFAULT (0),
    [TransactionDate] [datetime] NOT NULL CONSTRAINT [DF_TransactionHistory_TransactionDate] DEFAULT (GETDATE()),
    [TransactionType] [nchar](1) NOT NULL, 
    [Quantity] [int] NOT NULL,
    [ActualCost] [money] NOT NULL, 
    [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_TransactionHistory_ModifiedDate] DEFAULT (GETDATE()),
    CONSTRAINT [CK_TransactionHistory_TransactionType] CHECK (UPPER([TransactionType]) IN ('W', 'S', 'P'))
) ON [PRIMARY];
GO


CREATE TABLE [Production].[TransactionHistoryArchive](
    [TransactionID] [int] NOT NULL,
    [ProductID] [int] NOT NULL,
    [ReferenceOrderID] [int] NOT NULL,
    [ReferenceOrderLineID] [int] NOT NULL CONSTRAINT [DF_TransactionHistoryArchive_ReferenceOrderLineID] DEFAULT (0),
    [TransactionDate] [datetime] NOT NULL CONSTRAINT [DF_TransactionHistoryArchive_TransactionDate] DEFAULT (GETDATE()),
    [TransactionType] [nchar](1) NOT NULL, 
    [Quantity] [int] NOT NULL,
    [ActualCost] [money] NOT NULL, 
    [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_TransactionHistoryArchive_ModifiedDate] DEFAULT (GETDATE()),
    CONSTRAINT [CK_TransactionHistoryArchive_TransactionType] CHECK (UPPER([TransactionType]) IN ('W', 'S', 'P'))
) ON [PRIMARY];
GO

-- Bulk Insert the TransactionHistory and TransactionHistoryArchive data.
DECLARE 
     @retcode INT
    ,@data_path NVARCHAR(256)

SELECT @data_path = SUBSTRING([physical_name], 1, CHARINDEX(N'master.mdf', LOWER([physical_name])) - 1) 
FROM [master].[sys].[master_files] 
WHERE [database_id] = 1 
    AND [file_id] = 1;

SET @data_path = LEFT(@data_path, PATINDEX('%\MSSQL%', @data_path)) + '100\Tools\Samples\AdventureWorks OLTP\'

EXECUTE (N'BULK INSERT [Production].[TransactionHistory] FROM ''' + @data_path + N'TransactionHistory.csv''
WITH (
   CODEPAGE=''ACP'',
   DATAFILETYPE = ''char'',
   FIELDTERMINATOR= ''\t'',
   ROWTERMINATOR = ''\n'' ,
   KEEPIDENTITY,
   TABLOCK   
)');

EXECUTE (N'BULK INSERT [Production].[TransactionHistoryArchive] FROM ''' + @data_path + N'TransactionHistoryArchive.csv''
WITH (
   CODEPAGE=''ACP'',
   DATAFILETYPE = ''char'',
   FIELDTERMINATOR= ''\t'',
   ROWTERMINATOR = ''\n'' ,
   KEEPIDENTITY,
   TABLOCK   
)');
GO

ALTER TABLE [Production].[TransactionHistory] WITH CHECK ADD 
    CONSTRAINT [PK_TransactionHistory_TransactionID] PRIMARY KEY CLUSTERED 
    (
        [TransactionID]
    )  ON [PRIMARY];
GO

ALTER TABLE [Production].[TransactionHistoryArchive] WITH CHECK ADD 
    CONSTRAINT [PK_TransactionHistoryArchive_TransactionID] PRIMARY KEY CLUSTERED 
    (
        [TransactionID]
    )  ON [PRIMARY];
GO

CREATE INDEX [IX_TransactionHistory_ProductID] ON [Production].[TransactionHistory]([ProductID]) ON [PRIMARY];
CREATE INDEX [IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID] ON [Production].[TransactionHistory]([ReferenceOrderID], [ReferenceOrderLineID]) ON [PRIMARY];
GO

CREATE INDEX [IX_TransactionHistoryArchive_ProductID] ON [Production].[TransactionHistoryArchive]([ProductID]) ON [PRIMARY];
CREATE INDEX [IX_TransactionHistoryArchive_ReferenceOrderID_ReferenceOrderLineID] ON [Production].[TransactionHistoryArchive]([ReferenceOrderID], [ReferenceOrderLineID]) ON [PRIMARY];
GO

ALTER TABLE [Production].[TransactionHistory] ADD 
    CONSTRAINT [FK_TransactionHistory_Product_ProductID] FOREIGN KEY 
    (
        [ProductID]
    ) REFERENCES [Production].[Product](
        [ProductID]
    );
GO

CREATE TRIGGER [Production].[uTransactionHistory] ON [Production].[TransactionHistory] 
AFTER UPDATE NOT FOR REPLICATION AS 
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        UPDATE [Production].[TransactionHistory]
        SET [Production].[TransactionHistory].[ModifiedDate] = GETDATE()
        FROM inserted
        WHERE inserted.[TransactionID] = [Production].[TransactionHistory].[TransactionID];
    END TRY
    BEGIN CATCH
        EXECUTE [dbo].[uspPrintError];

        -- Rollback any active or uncommittable transactions before
        -- inserting information in the ErrorLog
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END

        EXECUTE [dbo].[uspLogError];
    END CATCH;
END;
GO

CREATE TRIGGER [Production].[uTransactionHistoryArchive] ON [Production].[TransactionHistoryArchive] 
AFTER UPDATE NOT FOR REPLICATION AS 
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        UPDATE [Production].[TransactionHistoryArchive]
        SET [Production].[TransactionHistoryArchive].[ModifiedDate] = GETDATE()
        FROM inserted
        WHERE inserted.[TransactionID] = [Production].[TransactionHistoryArchive].[TransactionID];
    END TRY
    BEGIN CATCH
        EXECUTE [dbo].[uspPrintError];

        -- Rollback any active or uncommittable transactions before
        -- inserting information in the ErrorLog
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END

        EXECUTE [dbo].[uspLogError];
    END CATCH;
END;
GO

EXECUTE [sys].[sp_addextendedproperty] N'MS_Description', N'Record of each purchase order, sales order, or work order transaction year to date.', N'SCHEMA', [Production], N'TABLE', [TransactionHistory], NULL, NULL;
EXECUTE [sys].[sp_addextendedproperty] N'MS_Description', N'Primary key for TransactionHistory records.', N'SCHEMA', [Production], N'TABLE', [TransactionHistory], N'COLUMN', [TransactionID];
EXECUTE [sys].[sp_addextendedproperty] N'MS_Description', N'Product identification number. Foreign key to Product.ProductID.', N'SCHEMA', [Production], N'TABLE', [TransactionHistory], N'COLUMN', [ProductID];
EXECUTE [sys].[sp_addextendedproperty] N'MS_Description', N'Purchase order, sales order, or work order identification number.', N'SCHEMA', [Production], N'TABLE', [TransactionHistory], N'COLUMN', [ReferenceOrderID];
EXECUTE [sys].[sp_addextendedproperty] N'MS_Description', N'Line number associated with the purchase order, sales order, or work order.', N'SCHEMA', [Production], N'TABLE', [TransactionHistory], N'COLUMN', [ReferenceOrderLineID];
EXECUTE [sys].[sp_addextendedproperty] N'MS_Description', N'Date and time of the transaction.', N'SCHEMA', [Production], N'TABLE', [TransactionHistory], N'COLUMN', [TransactionDate];
EXECUTE [sys].[sp_addextendedproperty] N'MS_Description', N'W = WorkOrder, S = SalesOrder, P = PurchaseOrder', N'SCHEMA', [Production], N'TABLE', [TransactionHistory], N'COLUMN', [TransactionType];
EXECUTE [sys].[sp_addextendedproperty] N'MS_Description', N'Product quantity.', N'SCHEMA', [Production], N'TABLE', [TransactionHistory], N'COLUMN', [Quantity];
EXECUTE [sys].[sp_addextendedproperty] N'MS_Description', N'Product cost.', N'SCHEMA', [Production], N'TABLE', [TransactionHistory], N'COLUMN', [ActualCost];
EXECUTE [sys].[sp_addextendedproperty] N'MS_Description', N'Date and time the record was last updated.', N'SCHEMA', [Production], N'TABLE', [TransactionHistory], N'COLUMN', [ModifiedDate];
GO

EXECUTE [sys].[sp_addextendedproperty] N'MS_Description', N'Transactions for previous years.', N'SCHEMA', [Production], N'TABLE', [TransactionHistoryArchive], NULL, NULL;
EXECUTE [sys].[sp_addextendedproperty] N'MS_Description', N'Primary key for TransactionHistoryArchive records.', N'SCHEMA', [Production], N'TABLE', [TransactionHistoryArchive], N'COLUMN', [TransactionID];
EXECUTE [sys].[sp_addextendedproperty] N'MS_Description', N'Product identification number. Foreign key to Product.ProductID.', N'SCHEMA', [Production], N'TABLE', [TransactionHistoryArchive], N'COLUMN', [ProductID];
EXECUTE [sys].[sp_addextendedproperty] N'MS_Description', N'Purchase order, sales order, or work order identification number.', N'SCHEMA', [Production], N'TABLE', [TransactionHistoryArchive], N'COLUMN', [ReferenceOrderID];
EXECUTE [sys].[sp_addextendedproperty] N'MS_Description', N'Line number associated with the purchase order, sales order, or work order.', N'SCHEMA', [Production], N'TABLE', [TransactionHistoryArchive], N'COLUMN', [ReferenceOrderLineID];
EXECUTE [sys].[sp_addextendedproperty] N'MS_Description', N'Date and time of the transaction.', N'SCHEMA', [Production], N'TABLE', [TransactionHistoryArchive], N'COLUMN', [TransactionDate];
EXECUTE [sys].[sp_addextendedproperty] N'MS_Description', N'W = Work Order, S = Sales Order, P = Purchase Order', N'SCHEMA', [Production], N'TABLE', [TransactionHistoryArchive], N'COLUMN', [TransactionType];
EXECUTE [sys].[sp_addextendedproperty] N'MS_Description', N'Product quantity.', N'SCHEMA', [Production], N'TABLE', [TransactionHistoryArchive], N'COLUMN', [Quantity];
EXECUTE [sys].[sp_addextendedproperty] N'MS_Description', N'Product cost.', N'SCHEMA', [Production], N'TABLE', [TransactionHistoryArchive], N'COLUMN', [ActualCost];
EXECUTE [sys].[sp_addextendedproperty] N'MS_Description', N'Date and time the record was last updated.', N'SCHEMA', [Production], N'TABLE', [TransactionHistoryArchive], N'COLUMN', [ModifiedDate];
GO

EXECUTE [sys].[sp_addextendedproperty] N'MS_Description', N'AFTER UPDATE trigger setting the ModifiedDate column in the TransactionHistory table to the current date.', N'SCHEMA', [Production], N'TABLE', [TransactionHistory], N'TRIGGER', [uTransactionHistory];
EXECUTE [sys].[sp_addextendedproperty] N'MS_Description', N'AFTER UPDATE trigger setting the ModifiedDate column in the TransactionHistoryArchive table to the current date.', N'SCHEMA', [Production], N'TABLE', [TransactionHistoryArchive], N'TRIGGER', [uTransactionHistoryArchive];
GO

EXECUTE [sys].[sp_addextendedproperty] N'MS_Description', N'Nonclustered index.', N'SCHEMA', [Production], N'TABLE', [TransactionHistory], N'INDEX', [IX_TransactionHistory_ProductID];
EXECUTE [sys].[sp_addextendedproperty] N'MS_Description', N'Nonclustered index.>', N'SCHEMA', [Production], N'TABLE', [TransactionHistory], N'INDEX', [IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID];
EXECUTE [sys].[sp_addextendedproperty] N'MS_Description', N'Clustered index created by a primary key constraint.', N'SCHEMA', [Production], N'TABLE', [TransactionHistory], N'INDEX', [PK_TransactionHistory_TransactionID];
GO

EXECUTE [sys].[sp_addextendedproperty] N'MS_Description', N'Nonclustered index.', N'SCHEMA', [Production], N'TABLE', [TransactionHistoryArchive], N'INDEX', [IX_TransactionHistoryArchive_ProductID];
EXECUTE [sys].[sp_addextendedproperty] N'MS_Description', N'Nonclustered index.', N'SCHEMA', [Production], N'TABLE', [TransactionHistoryArchive], N'INDEX', [IX_TransactionHistoryArchive_ReferenceOrderID_ReferenceOrderLineID];
EXECUTE [sys].[sp_addextendedproperty] N'MS_Description', N'Clustered index created by a primary key constraint.', N'SCHEMA', [Production], N'TABLE', [TransactionHistoryArchive], N'INDEX', [PK_TransactionHistoryArchive_TransactionID];
GO

EXECUTE [sys].[sp_addextendedproperty] N'MS_Description', N'Primary key (clustered) constraint', N'SCHEMA', [Production], N'TABLE', [TransactionHistory], N'CONSTRAINT', [PK_TransactionHistory_TransactionID];
EXECUTE [sys].[sp_addextendedproperty] N'MS_Description', N'Foreign key constraint referencing Product.ProductID.', N'SCHEMA', [Production], N'TABLE', [TransactionHistory], N'CONSTRAINT', [FK_TransactionHistory_Product_ProductID];
EXECUTE [sys].[sp_addextendedproperty] N'MS_Description', N'Default constraint value of 0', N'SCHEMA', [Production], N'TABLE', [TransactionHistory], N'CONSTRAINT', [DF_TransactionHistory_ReferenceOrderLineID]; -- ((0))
EXECUTE [sys].[sp_addextendedproperty] N'MS_Description', N'Default constraint value of GETDATE()', N'SCHEMA', [Production], N'TABLE', [TransactionHistory], N'CONSTRAINT', [DF_TransactionHistory_ModifiedDate]; -- (GETDATE())
EXECUTE [sys].[sp_addextendedproperty] N'MS_Description', N'Default constraint value of GETDATE()', N'SCHEMA', [Production], N'TABLE', [TransactionHistory], N'CONSTRAINT', [DF_TransactionHistory_TransactionDate]; -- (GETDATE())
EXECUTE [sys].[sp_addextendedproperty] N'MS_Description', N'Check constraint [TransactionType]=''p'' OR [TransactionType]=''s'' OR [TransactionType]=''w'' OR [TransactionType]=''P'' OR [TransactionType]=''S'' OR [TransactionType]=''W'')', N'SCHEMA', [Production], N'TABLE', [TransactionHistory], N'CONSTRAINT', [CK_TransactionHistory_TransactionType]; -- (UPPER([TransactionType])='P' OR UPPER([TransactionType])='S' OR UPPER([TransactionType])='W')
GO

EXECUTE [sys].[sp_addextendedproperty] N'MS_Description', N'Primary key (clustered) constraint', N'SCHEMA', [Production], N'TABLE', [TransactionHistoryArchive], N'CONSTRAINT', [PK_TransactionHistoryArchive_TransactionID];
EXECUTE [sys].[sp_addextendedproperty] N'MS_Description', N'Default constraint value of 0', N'SCHEMA', [Production], N'TABLE', [TransactionHistoryArchive], N'CONSTRAINT', [DF_TransactionHistoryArchive_ReferenceOrderLineID]; -- ((0))
EXECUTE [sys].[sp_addextendedproperty] N'MS_Description', N'Default constraint value of GETDATE()', N'SCHEMA', [Production], N'TABLE', [TransactionHistoryArchive], N'CONSTRAINT', [DF_TransactionHistoryArchive_ModifiedDate]; -- (GETDATE())
EXECUTE [sys].[sp_addextendedproperty] N'MS_Description', N'Default constraint value of GETDATE()', N'SCHEMA', [Production], N'TABLE', [TransactionHistoryArchive], N'CONSTRAINT', [DF_TransactionHistoryArchive_TransactionDate]; -- (GETDATE())
EXECUTE [sys].[sp_addextendedproperty] N'MS_Description', N'Check constraint [TransactionType]=''p'' OR [TransactionType]=''s'' OR [TransactionType]=''w'' OR [TransactionType]=''P'' OR [TransactionType]=''S'' OR [TransactionType]=''W''', N'SCHEMA', [Production], N'TABLE', [TransactionHistoryArchive], N'CONSTRAINT', [CK_TransactionHistoryArchive_TransactionType]; -- (UPPER([TransactionType])='P' OR UPPER([TransactionType])='S' OR UPPER([TransactionType])='W')
GO

USE [master];
GO
